package com.zx.db.mysql.document;

import java.io.FileOutputStream;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zx.db.mysql.dbutils.JdbclInit;
import com.zx.db.mysql.dbutils.MysqlJDbcUtils;

/**
 * 生成文档.
 * @author Ken
 * @version 2016年7月5日 下午11:12:30
 */
public class MysqlDocumentUtils {

	static Logger LOG = LoggerFactory.getLogger(MysqlDocumentUtils.class);
	private static XWPFDocument xDocument;


	/**
	 * 获取某个数据库下的某个表的结构信息.
	 * @param dataBaseName
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public static Map<String, MySqlDocumentMetaPo> getDatabaseInfo(String dataBaseName) throws Exception {
		Map<String, MySqlDocumentMetaPo> info = new HashMap<String, MySqlDocumentMetaPo>();
		String sql = "select TABLE_NAME,TABLE_TYPE,TABLE_COMMENT from  TABLES where TABLE_SCHEMA = ?";
		MysqlJDbcUtils jdbc = new MysqlJDbcUtils();
		List<Object> params = new ArrayList<Object>();
		params.add(dataBaseName);
		List<Map<String, Object>> colList = jdbc.findList(sql, params);
		if (null != colList && !colList.isEmpty()) {
			for (Map<String, Object> map : colList) {
				LOG.debug("table info : {}", map);
				String tableName = map.get("TABLE_NAME").toString();
				Map<String, MySqlDocumentMetaPo> infoTemp = getDatabaseTableInfo(dataBaseName,tableName);
				if(null != infoTemp && !infoTemp.isEmpty()){
					MySqlDocumentMetaPo po = infoTemp.get(tableName);
					po.setTableName(map.get("TABLE_COMMENT").toString());
					po.setTableType(map.get("TABLE_TYPE").toString());
					info.put(tableName, po);
				}
			}
		}
				
		return info;
	}
	
	/**
	 * 获取某个数据库下的某个表的结构信息.
	 * @param dataBaseName
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public static Map<String, MySqlDocumentMetaPo> getDatabaseTableInfo(String dataBaseName,
			String tableName) throws Exception {
		// table_schema='数据库名'
		String sql = "select table_name , column_name ,  column_type , column_key , extra , is_nullable ,column_comment from columns where table_schema=?  and table_name = ? ";
		MysqlJDbcUtils jdbc = new MysqlJDbcUtils();
		List<Object> params = new ArrayList<Object>();
		params.add(dataBaseName);
		params.add(tableName);
		List<Map<String, Object>> colList = jdbc.findList(sql, params);
		Map<String, MySqlDocumentMetaPo> info = new HashMap<String, MySqlDocumentMetaPo>();
		if (null != colList && !colList.isEmpty()) {
			for (Map<String, Object> map : colList) {
				LOG.debug("col info : {}", map);
				Object tName = map.get("TABLE_NAME");
				MySqlDocumentMetaPo po = info.get(tName.toString());
				if (po == null) {
					po = new MySqlDocumentMetaPo();
				}
				Object columnName = map.get("COLUMN_NAME");
				Map<String, LinkedHashMap<String, Object>> cols =  po.getCols();
				if(cols == null){
					cols = new HashMap<String, LinkedHashMap<String, Object>>();
				}
				LinkedHashMap<String, Object> columnInfo = cols.get(columnName.toString());
				if (columnInfo == null)
					columnInfo = new LinkedHashMap<String, Object>();
				Object key = map.get("COLUMN_KEY");
				if ("PRI".equals(key.toString())) {
					key = "是";
				}
				columnInfo.put("COLUMN_TYPE", map.get("COLUMN_TYPE"));
				columnInfo.put("COLUMN_KEY", map.get("COLUMN_KEY"));
				columnInfo.put("EXTRA", map.get("EXTRA"));
				columnInfo.put("IS_NULLABLE", map.get("IS_NULLABLE"));
				columnInfo.put("COLUMN_COMMENT", map.get("COLUMN_COMMENT"));
				LOG.debug("add colum : {}", columnInfo);
				cols.put(columnName.toString(), columnInfo);
				po.setCols(cols);
//				tablesMap.put(columnName.toString(), columnInfo);
				info.put(tName.toString(), po);
			}
		}

		return info;
	}

	/**
	 * 统一设定宽度.
	 */
	public static XWPFTableCell getCell(XWPFTableCell cell ){
		//					cell.setColor("ff0000"); //红色
		CTTcPr cellPr = cell.getCTTc().addNewTcPr();
		CTTblWidth cellw = cellPr.addNewTcW();
		cellw.setType(STTblWidth.DXA);
		cellPr.addNewTcW().setW(BigInteger.valueOf(1600));
		return cell;
	}
	
	/**
	 * 生成word，表格
	 * 
	 * @param data
	 * @throws Exception
	 */
	public static void productWordForm(Map<String, MySqlDocumentMetaPo> tables, String filePathNoSubFix) throws Exception {
		xDocument = new XWPFDocument();
		Iterator<String> tableNameIter = tables.keySet().iterator();
		while (tableNameIter.hasNext()) {
			String table_name = tableNameIter.next();
			MySqlDocumentMetaPo po = tables.get(table_name);
			Map<String, LinkedHashMap<String, Object>> columns = po.getCols();
			LOG.debug("columns : --- {}", columns);
			XWPFParagraph xp = xDocument.createParagraph();
			XWPFRun r1 = xp.createRun();
			String view = "";
			if(po.getTableType() != null && po.getTableType().equalsIgnoreCase("VIEW")){
				view = "  (VIEW)";
			}
			String name = "";
			if(po.getTableName() != null){
				name = po.getTableName();
			}
			r1.setText(table_name + " " +name  + view);
			r1.setFontSize(18);
			r1.setTextPosition(10);
			int rows = columns.size();
			XWPFTable xTable = xDocument.createTable(rows + 1, 6);
//			xTable.setWidth(210);
			int i = 0;
			getCell(xTable.getRow(i).getCell(0)).setText("列名");
			getCell(xTable.getRow(i).getCell(1)).setText("类型");
			getCell(xTable.getRow(i).getCell(2)).setText("主键");
			getCell(xTable.getRow(i).getCell(3)).setText("备注");
			getCell(xTable.getRow(i).getCell(4)).setText("可为NULL");
			getCell(xTable.getRow(i).getCell(5)).setText("说明");
			
			i = i + 1;// 下一行
			int j = 0;// 列column索引

			Map<String, LinkedHashMap<String, Object>> keyColumnMap = keyColumns(columns);
			for (Iterator<String> columnNameIter = keyColumnMap.keySet().iterator(); columnNameIter.hasNext();) {
				String column_name = columnNameIter.next();
				LinkedHashMap<String, Object> columnsAtt = keyColumnMap.get(column_name);
				getCell(xTable.getRow(i).getCell(j)).setText(column_name);
				++j;

				Iterator<String> columnTypeIter = columnsAtt.keySet().iterator();

				while (columnTypeIter.hasNext()) {
					String colum_type = columnTypeIter.next();
					getCell(xTable.getRow(i).getCell(j)).setText(columnsAtt.get(colum_type).toString());
					j++;
				}

				++i;// 下一行
				j = 0;// 恢复第一列
			}

			Iterator<String> cloumnsNameIter = columns.keySet().iterator();

			while (cloumnsNameIter.hasNext()) {
				String colum_name = cloumnsNameIter.next();
				LinkedHashMap<String, Object> columnsAtt = columns.get(colum_name);
				if (xTable.getRow(i) == null)
					continue;
				getCell(xTable.getRow(i).getCell(j)).setText(colum_name);

				j++;

				Iterator<String> columnTypeIter = columnsAtt.keySet().iterator();
				//				j = 0;
				while (columnTypeIter.hasNext()) {
					String colum_type = columnTypeIter.next();
					LOG.debug(colum_type + "---" + columnsAtt.get(colum_type) + "---" + i + "----" + j);
//					xTable.getRow(i).getCell(j).setText(columnsAtt.get(colum_type).toString());
					getCell(xTable.getRow(i).getCell(j)).setText(columnsAtt.get(colum_type).toString());
					j++;
				}
				j = 0;// 恢复第一列
				++i; //下一行
			}
		}

		FileOutputStream fos = new FileOutputStream(filePathNoSubFix);

		xDocument.write(fos);
		fos.close();
	}

	/**
	 * 检索出主键key相关的列
	 * 
	 * @param columnsMap
	 * @return
	 */
	private static Map<String, LinkedHashMap<String, Object>> keyColumns(
			Map<String, LinkedHashMap<String, Object>> columnsMap) {
		Map<String, LinkedHashMap<String, Object>> keyColumnMap = new HashMap<String, LinkedHashMap<String, Object>>();

		Iterator<String> cloumnsNameIter = columnsMap.keySet().iterator();
		while (cloumnsNameIter.hasNext()) {
			String colum_name = cloumnsNameIter.next();
			LinkedHashMap<String, Object> columnsAtt = columnsMap.get(colum_name);
			if (columnsAtt.get("COLUMN_KEY").toString().equals("是")) {
				keyColumnMap.put(colum_name, columnsAtt);
				cloumnsNameIter.remove();
			}
		}

		return keyColumnMap;
	}
	
	/**
	 * userVal, passwordVal, driverVal, urlVal,dbName,tableName(如果空表示生成全库),全路径( "E:\\Test_doc.docx")
	 *  java -jar  MySqlDocument.jar root  hx123  com.mysql.jdbc.Driver  jdbc:mysql://127.0.0.1:3306/information_schema test E:\\Test_doc.docx 
	 *  java -jar  MySqlDocument.jar root  hx123  com.mysql.jdbc.Driver  jdbc:mysql://127.0.0.1:3306/information_schema test Document_User E:\\Test_doc.docx 
	 *	productWordForm(getDatabaseInfo("test"), "E:\\Test.docx");
	 *	productWordForm(getDatabaseTableInfo("test","document_table2"), "E:\\Test.docx");
		productWordForm(getDatabaseInfo("fjq"), "E:\\FJq.docx");
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		JdbclInit.getInstance(args[0], args[1], args[2], args[3]);
		if(args.length < 7 ){
			productWordForm(getDatabaseInfo(args[4]), args[5]); 
		}else{
			productWordForm(getDatabaseTableInfo(args[4],args[5]), args[6]);
		}
	}

}
